CREATE PROCEDURE pro_declare_variable ()
BEGIN 
  SELECT" noas ";
END

CALL pro_declare_variable();

DROP PROCEDURE pro_declare_variable;

SHOW SESSION VARIABLES; 
USE dbo;

-- 查询女生中最小年龄，并根据年龄输出   未成年人（0-17） 成年人（18-59）  老年人（>=60）
CREATE PROCEDURE pro_if()
BEGIN 
DECLARE minage TINYINT;
SELECT MIN(sage) into minage FROM student WHERE ssex="nv";
IF minage>0 AND minage<17 THEN
SELECT"未成年人";
ELSEIF minage>17 AND minsage<60 THEN
SELECT"成年人";
ELSE 
SELECT"老年人";
end if;

END

call pro_if;
-- 输出1—10 5的时候结束循环
create  procedure  pro_while_leave()
begin
declare i int default 1;
outs: while  i<=10   do
select  i;
if  i=5  then
leave  outs;
end  if;
set  i=i+1;
end  while;
end

call  pro_while_leave;
DROP procedure pro_while_leave;

-- 输出1~100的和
create  procedure  pro_while()
begin
declare  i   int    default  1;
declare   total   int   default  0;
while  i<=100   do
set   total =total+i;
set   i=i+1;
end  while;
select  total;

end
CALL pro_while;


-- 定义一个存储过程，传入一个varchar型的数据，表示学号，获得该学生的平均成绩，并返回该成绩
use dbo;
-- 带输入、输出参数的
CREATE PROCEDURE pro_avgscore(in snum VARCHAR(20),out avg FLOAT)
BEGIN
SELECT avg(score) into avg FROM sc WHERE snum=sno;
END

call pro_avgscore("s001",@avgsc);
SELECT  @avgsc;

-- 只带输入参数的
CREATE PROCEDURE pro_avg1(in snum varchar(20))
BEGIN
SELECT avg(score) FROM sc WHERE snum=sno;
END

call pro_avg1("s001");

use dbo;
-- 定义一个存储过程，传入一个int型的数据，表示年龄，找到成绩表中该年龄所对应学生的最高成绩，并返回该成绩
CREATE PROCEDURE pro_score(in age INT,out maxscore FLOAT)
BEGIN
SELECT MAX(score) INTO maxscore FROM sc WHERE sno IN (SELECT sno FROM student where sage = age );

END

CALL pro_score(23,@userscore);
SELECT @userscore;

-- 检查年龄，如果年龄<0或年龄>120，默认为0  检查性别，如果不为男和女，默认为男
create trigger  tri_student_age BEFORE INSERT ON student FOR each ROW
BEGIN
IF new.sage<0 OR new.sage>120 THEN
SET new.sage=0;
end if;
IF new.ssex !="男" AND  new.ssex !="女" THEN 
SET new.ssex = "男";
END IF;
END


DROP TRIGGER tri_student_age;
INSERT INTO student VALUES ("s012","陈华",121,"人");
INSERT INTO student VALUES ("s011","mary",-2,"啊");


-- 更新学生表，确保 年龄和性别更新后的数据是合理的，不合理则先判断以前的值是否合理，如果合理则使用更新之前的，否则就使用默认值
CREATE TRIGGER tri_student_age_sex BEFORE UPDATE on student FOR EACH ROW
BEGIN
IF new.sage <0 OR new.sage>120 THEN
  IF old.sage>=0 AND old.sage<=120 THEN
   SET new.sage=old.sage;
  ELSE
   SET new.sage=0;
  END IF;
END IF;
IF new.ssex !="男"AND new.ssex !="女" THEN
  IF old.ssex !="男"AND old.ssex !="女" THEN
    set new.ssex="男";
  else 
  set new.ssex=old.ssex;
  END IF;
END IF;

END
 
drop TRIGGER tri_student_age_sex;

-- 事务 --
BEGIN;
INSERT INTO student VALUES ("s183","长",19,"r");
UPDATE student SET sno="s243" WHERE ss=0;

ROLLBACK;
COMMIT;


-- 条件处理器 --

-- 创建存储过程，开启一个事务，里面向成绩表添加数据，如果添加失败，则回滚。
CREATE PROCEDURE pro_rollback()
BEGIN
DECLARE  CONTINUE  HANDLER  FOR  SQLEXCEPTION  ROLLBACK;
START TRANSACTION;
INSERT INTO student VALUES ("s034","jqi",34,"女");
UPDATE student SET ss=3;
COMMIT;
END

-- 定义游标，查询学生表中的所有数据并将结果集保存在游标中，输出里面的第一条数据
create  procedure  pro_cursor()
begin
-- 定义变量用于游标的数据
declare  sname   varchar(20);
declare  sno  varchar(20);
declare  sage  int;
declare  ssex  char(2);
-- 定义游标
declare cur_student cursor  for  select  *  from  student;
-- 打开游标
open cur_student;
-- 取值
fetch  cur_student  into  sno,sname,sage,ssex;
-- 输出
select  sno,sname,sage,ssex;
-- 关闭游标
close cur_student;
end

CALL pro_cursor;
DROP PROCEDURE pro_cursor;


-- 定义游标，查询学生表中的所有数据并将结果集保存在游标中，输出里面的每一条数据
CREATE PROCEDURE  pro_cursor1()
BEGIN
DECLARE sno varchar(20);
DECLARE sname varchar(20);
DECLARE sage INT;
DECLARE ssex char(2);
DECLARE iserror INT DEFAULT 0;
DECLARE cur_cursor1 CURSOR FOR select * FROM student;
DECLARE CONTINUE HANDLER FOR NOT found SET iserror=1;
OPEN cur_cursor1;
WHILE iserror=0 DO
FETCH cur_cursor1 INTO sno,sname,sage,ssex;
SELECT sno,sname,sage,ssex;
end WHILE;
CLOSE cur_cursor1;

END

call pro_cursor1();
drop procedure pro_cursor1;

-- 定义游标，查询学生表中的所有数据并将结果集保存在游标中，输出里面的每一条数据
CREATE PROCEDURE pro_cur()
BEGIN
DECLARE sno varchar(20);
DECLARE sname VARCHAR(20);
DECLARE sage INT;
DECLARE ssex char(2);
DECLARE iserror int DEFAULT 0;
DECLARE cur_student cursor FOR SELECT * FROM student;
DECLARE CONTINUE HANDLER FOR NOT found SET iserror=1;
OPEN cur_student;
WHILE iserror=0 DO 
FETCH cur_student INTO sno,sname,sage,ssex;
SELECT sno,sname,sage,ssex;
END WHILE;
CLOSE cur_student;

END

call pro_cur();

DROP PROCEDURE pro_cur;


USE dbo;
DROP trigger tri_update;
-- 创建触发器，用于检查更新成绩表的成绩更新是否合理，如果不合理，则默认为0（合理范围  0~100）
CREATE TRIGGER tri_update BEFORE UPDATE ON sc FOR EACH ROW
BEGIN
IF new.score<0 OR new.score>100 THEN
SET new.score=0;
END IF;

END
UPDATE sc set score=212 where sno='s001' AND cno ='c002';
SELECT * FROM sc;

-- 创建存储过程，传入一个课程编号，找出成绩表中该课程的最高分并输出
CREATE PROCEDURE pro_maxscore(IN cno1 varchar(20),OUT max FLOAT)
BEGIN
SELECT max(score) into max FROM sc WHERE cno=cno1;
END

CALL pro_maxscore("c001",@maxsc);
SELECT @maxsc;

DROP PROCEDURE pro_maxscore;
